﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.IO;
using Microsoft.Win32;

namespace ImportUtility
{


    public class BioFingerUtility : IImportUtility
    {
        RegistryKey regKey = Registry.CurrentUser.OpenSubKey("SOFTWARE\\eBird\\eOffice1.0");
            
        public event EventHandler UpdateImportStatus;
        #region Properties
        string _filename;

        public string Filename
        {
            get { return _filename; }
            set { _filename = value; }
        }
        string _strCon;

        public string StrCon
        {
            get { return _strCon; }
            set { _strCon = value; }
        }
        int _deviceId;

        public int DeviceId
        {
            get { return _deviceId; }
            set { _deviceId = value; }
        }
        string _deviceName;

        public string DeviceName
        {
            get { return _deviceName; }
            set { _deviceName = value; }
        }

        bool _bDup;

        public bool BDup
        {
            get { return _bDup; }
            set { _bDup = value; }
        }
        

        #endregion Properties

        public bool Init()
        {
            if (regKey != null)
                                      _strCon = regKey.GetValue("ConctString").ToString();

            _deviceId = 1;
            _deviceName = "BioFinger";
            //_strCon = Properties.Settings.Default.constr;                
            return true;
        }
        public bool SetFile(string _filename1)
        {
            if (File.Exists(_filename1))
            {
                Filename = _filename1;
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool Import(bool _bDuplicate)
        {
            Thread _utilityThread = new Thread(new ThreadStart(UserThread));
            _utilityThread.Start();
            _bDup = _bDuplicate;
            return true;
        }

      
        private void UserThread()
        {
            UpdateImportStatusEvent updateImportStatus = new UpdateImportStatusEvent();
            try
            {
               
                updateImportStatus.ImportStatus = UpdateStatus.ImportStarted;
                UpdateImportStatus(this, updateImportStatus);

                ++updateImportStatus.ProgressCounter;
                
                long i = 0;
                //string filename = textBox1.Text.ToString();
                StreamReader sr = new StreamReader(_filename);
                string line = sr.ReadLine();
                SqlConnection dbConn = new SqlConnection(_strCon);
                SqlConnection dbConnbranch = new SqlConnection(_strCon);
                SqlConnection dbConncomp = new SqlConnection(_strCon);
              
                SqlCommand dbCmd = new SqlCommand();
                SqlCommand dbCmdbranch = new SqlCommand();
                SqlCommand dbCmdcompany = new SqlCommand();

                dbCmd.Connection = dbConn;
                dbCmdbranch.Connection = dbConnbranch;
                dbCmdcompany.Connection = dbConncomp;

                SqlDataReader reader;
                SqlDataAdapter sqladt;
                DataSet ds;
                
                string _empId = "";
                int _deviceId = 0;
                int branchid = 0;
                int empstatus = 1;
                int cmpid = 0;
                while (line != null)
                {
                    string[] fields = line.Split(',');
                    dbConncomp.Open();
                    dbConnbranch.Open();
                    dbConn.Open();
                    
                    dbCmdbranch.CommandText = "SELECT TOP 1 BRBRANCHID FROM BRANCH ORDER BY BRBRANCHID ASC";

                    reader = dbCmdbranch.ExecuteReader();
                   
                    while (reader.Read())
                    {
                        branchid = int.Parse(reader[0].ToString());
                    }
                    reader.Close();

                     dbCmdcompany.CommandText="SELECT TOP 1 cmpid FROM company ORDER BY cmpid ASC";
                     reader = dbCmdcompany.ExecuteReader();

                     while (reader.Read())
                     {
                         cmpid = int.Parse(reader[0].ToString());
                     }
                     reader.Close();

                     if (cmpid == 0 && branchid == 0)
                     {
                         dbConn.Close();
                         dbConnbranch.Close();
                         dbConncomp.Close();
                         i = i + 1;
                         line = sr.ReadLine();

                         updateImportStatus.ImportStatus = UpdateStatus.ImportFailed;
                         updateImportStatus.StatusMessage = "You must be add company and branch details before Userlist import..";
                         UpdateImportStatus(this, updateImportStatus);
                         return;
                         
                     }
                     else if (_bDup)
                     {
                         dbCmd.CommandText = "SELECT      DevEmpDetails.CardID " +
                             " FROM         DevEmpDetails INNER JOIN EmployDetails ON " +
                             " DevEmpDetails.EmpID = EmployDetails.EmpId " +
                             " where DevEmpDetails.CardID='" + fields[0].ToString() + "' and EmployDetails.EmpStatus=1";
                         //reader = dbCmd.ExecuteReader();
                         sqladt = new SqlDataAdapter(dbCmd);
                         ds = new DataSet();
                         sqladt.Fill(ds);
                         if (ds.Tables[0].Rows.Count==0)
                         {
                             //SqlDataAdapter DB;
                             //DataSet employeeDS=new DataSet();
                             //string query = "SELECT      DevEmpDetails.CardID " +
                             //" FROM         DevEmpDetails INNER JOIN EmployDetails ON " +
                             //" DevEmpDetails.EmpID = EmployDetails.EmpId " +
                             //" where DevEmpDetails.CardID='" + fields[0].ToString() + "' and EmployDetails.EmpStatus=1";

                             //DB = new SqlDataAdapter(query, _strCon);
                             //DB.Fill(employeeDS); 

                             //if (employeeDS.Tables[0].Rows.Count>0)
                             // {
                             //     break; 
                             // }

                             // else
                             // {
                                  dbCmd.CommandText = "INSERT INTO EmployDetails(EmpFirstName,EmpLastName,EmpBrId,EmpStatus,EmpCmpId) VALUES ('" + fields[2].ToString() + "','" + fields[3].ToString() + "'," + branchid + "," + empstatus + " ," + cmpid + ")";
                                  dbCmd.ExecuteNonQuery();
                                  dbCmd.CommandText = "select max(EmpId) from EmployDetails";
                                  reader = dbCmd.ExecuteReader();
                                  if (reader.Read())
                                      _empId = reader[0].ToString();
                                  reader.Close();

                                  string _empCode = fields[2].ToString().Substring(0, 3) + _empId.ToString();
                                  dbCmd.CommandText = "UPDATE EmployDetails set EmpCode = '" + _empCode + "' where EmpId = " + _empId;
                                  dbCmd.ExecuteNonQuery();
                                  dbCmd.CommandText = "INSERT INTO DevEmpDetails(EmpID,DeviceName,DeviceID,DevEmpID,CardID) VALUES (" + int.Parse(_empId.ToString()) + ",'" + _deviceName + "'," + _deviceId + ",'" + fields[1].ToString() + "','" + fields[0].ToString() + "')";
                                  dbCmd.ExecuteNonQuery();
                             // }
                             
                         }
                         else
                         {
                             string _cardId = ds.Tables[0].Rows[0][0].ToString();
                             string _empCode = fields[2].ToString().Substring(0, 3) + _cardId;
                             dbCmd.CommandText = "UPDATE EmployDetails set EmpCode = '" + _empCode + "', EmpFirstName='" + fields[2].ToString() + "',EmpLastName='" + fields[3].ToString() + "',EmpBrId=" + branchid + ",EmpStatus=" + empstatus + ",EmpCmpId=" + cmpid + " where EmpId='" + _cardId + "'";
                             dbCmd.ExecuteNonQuery();
                             dbCmd.CommandText = "UPDATE DevEmpDetails set DeviceName='" + _deviceName + "',DeviceID=" + _deviceId + ",DevEmpID='" + fields[1].ToString() + "',CardID='" + fields[0].ToString() + "' where EmpID="+int.Parse(_cardId.ToString())+"";
                             dbCmd.ExecuteNonQuery();
                            
                         }
                         reader.Close();

                     }

                     //else
                     //{
                     //    dbCmd.CommandText = "INSERT INTO EmployDetails(EmpFirstName,EmpLastName,EmpBrId,EmpStatus,EmpCmpId) VALUES ('" + fields[2].ToString() + "','" + fields[3].ToString() + "'," + branchid + "," + empstatus + " ," + cmpid + ")";
                     //    dbCmd.ExecuteNonQuery();
                     //    dbCmd.CommandText = "select max(EmpId) from EmployDetails";
                     //    reader = dbCmd.ExecuteReader();
                     //    if (reader.Read())
                     //        _empId = reader[0].ToString();
                     //    reader.Close();

                     //    string _empCode = fields[2].ToString().Substring(0, 3) + _empId.ToString();
                     //    dbCmd.CommandText = "UPDATE EmployDetails set EmpCode = '" + _empCode + "' where EmpId = " + _empId;
                     //    dbCmd.ExecuteNonQuery();
                     //    dbCmd.CommandText = "INSERT INTO DevEmpDetails(EmpID,DeviceName,DeviceID,DevEmpID,CardID) VALUES (" + int.Parse(_empId.ToString()) + ",'" + _deviceName + "'," + _deviceId + ",'" + fields[1].ToString() + "','" + fields[0].ToString() + "')";
                     //    dbCmd.ExecuteNonQuery();
                         
                     //}
                     dbConn.Close();
                     dbConnbranch.Close();
                     dbConncomp.Close();
                    i = i + 1;
                    line = sr.ReadLine();

                    updateImportStatus.ImportStatus = UpdateStatus.ImportProgressing;
                    updateImportStatus.StatusMessage = "Progressing...";
                    UpdateImportStatus(this, updateImportStatus);
                    ++updateImportStatus.ProgressCounter;

                    
                }
                updateImportStatus.ImportStatus = UpdateStatus.ImportCompleted;
                updateImportStatus.StatusMessage = "Finished";
                UpdateImportStatus(this, updateImportStatus);
                                    
            }
            catch (Exception ex)
            {
                updateImportStatus.ImportStatus = UpdateStatus.ImportFailed;
                updateImportStatus.StatusMessage = ex.Message;
                UpdateImportStatus(this, updateImportStatus);
            }
        }

       
    }
   

    
}
